package com.opennetexam.manager.sqlprovider;

import com.opennetexam.manager.enums.ParameterKey;
import com.opennetexam.manager.utils.CommonUtil;
import org.apache.log4j.Logger;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/14.
 */
public class ExamineeArrangeProvider {

    private static Logger logger = Logger.getLogger(ExamineeArrangeProvider.class);

    public static String findExamPlanViewModelBySomeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String siteId = (String) parameter.get("siteId");
        String examPlanName = (String) parameter.get("examPlanName");
        Short planType = Short.valueOf(parameter.get("planType").toString());
        stringBuffer.append("SELECT dataplan.AppID,dataplan.ExamPlanInfoID batchId,dataplan.ExamPlanName,dataplan.ExamMode,datasubcount.count as ExamSubjectCount,dataplan.BeginTime,dataplan.EndTime,dataplan.SysState,dataplan.ExamPlanType,dataplan.SeatByNumberRequired FROM ExamPlanInfo AS dataplan");
        stringBuffer.append(" JOIN ( SELECT dataSub.ExamPlanInfoID,dataSub.SubjectPaperStatus,COUNT(*) AS count FROM ExamSubject AS dataSub GROUP BY dataSub.ExamPlanInfoID,dataSub.AppID ) AS datasubcount ON dataplan.ExamPlanInfoID = datasubcount.ExamPlanInfoID");
        stringBuffer.append(" WHERE dataplan.ExamSiteID = \'").append(siteId).append("\' ").append(" AND dataplan.ExamPlanType = ").append(planType).append(" AND dataplan.AppID = \'").append(appId).append("\'");
        if(!StringUtils.isEmpty(examPlanName)){
            stringBuffer.append(" AND dataplan.ExamPlanName LIKE \'%").append(examPlanName).append("%\'");
        }
        stringBuffer.append(" ORDER BY dataplan.BeginTime");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String getGeneratedCount(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String appId = (String) parameter.get("appId");

        stringBuffer.append("SELECT\n" +
                "\tcount(1)\n" +
                "FROM\n" +
                "ExamSubject AS y\n" +
                "LEFT JOIN ExamineeExamPaper AS x ON x.ExamPlanInfoID = y.ExamPlanInfoID\n" +
                "WHERE");

        stringBuffer.append(" x.ExamPlanInfoID = \'").append(examPlanInfoID).append("\'");
        stringBuffer.append(" AND x.ExamSubjectID = y.ExamSubjectID AND x.AppID = y.AppID AND x.AppID = \'").append(appId).append("\'");


        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findArranges(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String planId = (String) parameter.get("planId");
        String appId = (String) parameter.get("appId");

        stringBuffer.append("SELECT arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamSubjectID,arrange.ExamSceneID,COUNT(*) AS count FROM ExamineeArrange AS arrange JOIN ( ");
        stringBuffer.append("SELECT * FROM ExamPlanInfo WHERE ExamPlanInfoID = \'").append(planId).append("\' ").append(" AND AppID = \'").append(appId).append("\' ) AS data1 ");
        stringBuffer.append("ON arrange.ExamPlanInfoID = data1.ExamPlanInfoID WHERE arrange.AppID = data1.AppID GROUP BY arrange.AppID, arrange.ExamPlanInfoID, arrange.ExamSubjectID, arrange.ExamSceneID");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findExamComputerBySomeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoId = (String) parameter.get("examPlanInfoId");

        stringBuffer.append("SELECT x.* FROM ExamComputer AS x JOIN ");
        stringBuffer.append("( SELECT * FROM ExamineeArrange WHERE AppID = \'").append(appId).append("\' AND ExamPlanInfoID = \'").append(examPlanInfoId).append("\' ) AS y ");
        stringBuffer.append("ON x.SeatNo = y.SeatNo");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examineeArrangeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examSubjectID = (String) parameter.get("examSubjectID");

        stringBuffer.append("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = \'").append(examPlanInfoID).append("\' AND ExamSubjectID = \'").append(examSubjectID).append("\' AND AppID = \'").append(appId).append("\'");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examineeNotArrangeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examSubjectID = (String) parameter.get("examSubjectID");

        stringBuffer.append("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = \'").append(examPlanInfoID).append("\' AND ExamSubjectID = \'").append(examSubjectID).append("\' AND AppID = \'").append(appId).append("\' AND ExamineeArrangeID NOT IN ");
        stringBuffer.append("( SELECT a.ExamineeArrangeID FROM ExamineeArrange AS a JOIN ExamineeExamPaper AS p ON a.ExamPlanInfoID = p.ExamPlanInfoID AND a.ExamSubjectID = p.ExamSubjectID AND p.ExamineeID = a.ExamineeID )");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examineeArrangeMoniter(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examRoomID = (String) parameter.get("examRoomID");

        stringBuffer.append("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = \'").append(examPlanInfoID).append("\' ").append(" AND ExamRoomID = \'").append(examRoomID).append("\' ");
        if(parameter.get("examineeID") != null){
            stringBuffer.append(" AND ExamineeID = \'").append((String) parameter.get("examineeID")).append("\' ");
        }
        if(parameter.get("appID") != null){
            stringBuffer.append(" AND AppID = \'").append((String)parameter.get("appID")).append("\'");
        }
        if(parameter.get("examSceneID") != null){
            stringBuffer.append(" AND ExamSceneID = \'").append((String)parameter.get("examSceneID")).append("\' ");
        }
        if(parameter.get("examSubjectID") != null){
            stringBuffer.append(" AND ExamSubjectID = \'").append((String)parameter.get("examSubjectID")).append("\' ");
        }

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    /**
     * 获取意外列表
     * @param parameter
     * @return
     */
    public String findAccidentList(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();

        Map<String,Object> requestBody = (Map<String, Object>) parameter.get("requestBody");
        String appId = (String) parameter.get("appId");

        String examineeName = "";//学生姓名
        String examCardNo = "";//准考证号
        String planId = (String) requestBody.get(ParameterKey.PLANID.getDisplay());//计划编号
        String roomId = (String) requestBody.get("roomId");//考场编号
        String sceneId = (String) requestBody.get(ParameterKey.SCENEID.getDisplay());//场次编号
        String subjectId = (String) requestBody.get("subjectId");//科目编号
        Integer statusId = (Integer) requestBody.get("statusId");//状态
        String param = (String) requestBody.get("parameter");
        // 如果是汉字则为名称检索，否则为准考证号检索
        if(!StringUtils.isEmpty(param)){
            String reg = "^[\\u4e00-\\u9fa5]+$";
            if(CommonUtil.matchesStr(param,reg)){
                examineeName = param.trim();
            }else {
                examCardNo = param.trim();
            }
        }

        stringBuffer.append("SELECT \n" +
                "DISTINCT e.ExamineeID as examineeId,\n" +
                "e.ExamineeName as examineeName,\n" +
                "e.ExamCardNo as cardNo,\n" +
                "es.SubjectName as subjectName,\n" +
                "em.SysState as syste,\n" +
                "ea.PlanBeginTime as examStartTime \n" +
                "FROM ExamineeArrange ea  \n" +
                "INNER JOIN ExamingMonitor em ON ea.ExamPlanInfoID = em.ExamPlanInfoID AND ea.ExamineeID = em.ExamineeID AND ea.AppID = em.AppID \n" +
                "INNER JOIN ExamSubject es ON ea.ExamSubjectID = es.ExamSubjectID \n" +
                "INNER JOIN Examinee e ON ea.ExamineeID = e.ExamineeID \n" +
                "WHERE 1 = 1 ");

        stringBuffer.append(" AND ea.AppID = \'").append(appId).append("\'").append(" AND ea.ExamPlanInfoID = \'").append(planId).append("\' AND ea.ExamSceneID = \'").append(sceneId).append(" \' ");
        if(roomId != null){
            stringBuffer.append(" AND ea.ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(subjectId != null){
            stringBuffer.append(" AND ea.ExamSubjectID = \'").append(subjectId).append("\' ");
        }
        if(statusId != null){
            stringBuffer.append(" AND em.SysState =").append(statusId);
        }
        if(!StringUtils.isEmpty(examineeName)){
            stringBuffer.append(" AND e.ExamineeName like \'%").append(examineeName).append("%\'");
        }
        if(!StringUtils.isEmpty(examCardNo)){
            stringBuffer.append(" AND e.ExamCardNo like \'%").append(examCardNo).append("%\'");
        }

        String sql = stringBuffer.toString();
        return sql;
    }

    public static void main(String[] args){
        Map<String,Object> map = new HashMap<>();
        map.put("appID","1");
        map.put("examPlanInfoID",66);
        map.put("examRoomID",21);
        map.put("examSubjectID",246);
//        System.out.println(examineeArrangeMoniter(map));
    }
}
